﻿
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;

namespace broswersugar
{
    public class SqlSugarHelper
    {
        #region 属性
        private SqlSugarClient db;
        bool IsDebug = false;
        #endregion
        public SqlSugarClient Instance()
        {
            if (db == null)
            {
                string dbpath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Websites.db");
                db = new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = string.Format("DataSource = {0}; Version = 3;", dbpath), //必填
                    DbType = DbType.Sqlite, //必填
                    IsAutoCloseConnection = true, //默认false
                                                  // InitKeyType = InitKeyType.SystemTable
                }); //默认SystemTable
                return db;
            }
            return db;
        }
        private static SqlSugarClient singleton;
        static readonly object Padlock = new object();
        public static SqlSugarClient GetInstance()
        {
            if (singleton == null)
            {
                lock (Padlock)
                {
                    if (singleton == null)
                    {
                        string dbpath = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Websites.db");
                        singleton = new SqlSugarClient(new ConnectionConfig()
                        {
                            ConnectionString = string.Format("DataSource = {0}; Version = 3;", dbpath), //必填
                            DbType = DbType.Sqlite, //必填
                                                    // IsAutoCloseConnection = true, //默认false
                                                    // InitKeyType = InitKeyType.SystemTable
                        }); //默认SystemTable
                        return singleton;
                    }
                }
            }
            return singleton;
        }
        #region 构造函数初始化
        /// <summary>
        /// 默认连接字符串 
        /// </summary>
        public SqlSugarHelper()
        {
            //string ConnectionString = System.IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "LHWeight.db");// ConfigurationManager.AppSettings["default"]; ;
            //db = new SqlSugarClient(new ConnectionConfig()
            //{
            //    ConnectionString = ConnectionString, //必填
            //    DbType = DbType.Sqlite, //必填
            //    IsAutoCloseConnection = true, //默认false
            //    InitKeyType = InitKeyType.SystemTable
            //}); //默认SystemTable

            //string _isDebug = "";//ConfigurationManager.AppSettings["IsDebug"];
            //if (!string.IsNullOrEmpty(_isDebug) && _isDebug == "1")
            //{
            //    IsDebug = true;
            //}
        }
        /// <summary>
        /// 指定连接字符串 
        /// </summary>
        public SqlSugarHelper(string _connectionString)
        {
            string ConnectionString = "";// ConfigurationManager.AppSettings[_connectionString]; ;
            db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = ConnectionString, //必填
                DbType = DbType.SqlServer, //必填
                IsAutoCloseConnection = true, //默认false
                InitKeyType = InitKeyType.SystemTable
            }); //默认SystemTable

            string _isDebug = "";// ConfigurationManager.AppSettings["IsDebug"];
            if (!string.IsNullOrEmpty(_isDebug) && _isDebug == "1")
            {
                IsDebug = true;
            }

        }


        #endregion

        #region 查询
        /// <summary>
        /// 读取实体数据 查询一定数量的数据 topNum为0则读取所有数据
        /// </summary>
        public List<T> GetList<T>(string tblName, string getFileds, string orders, int topNum, string strWhere, params object[] parameterValue) where T : new()
        {
            strWhere = SqlStatementParametered(strWhere);
            List<SugarParameter> listSugarParameter = ChangeSugarParameter(parameterValue);
            //设置排序
            if (!string.IsNullOrEmpty(orders))
                orders = " order by " + orders + " ";
            //top
            string topnums = "";
            if (topNum != 0)
                topnums = " top " + topNum + " ";
            //where
            if (!string.IsNullOrEmpty(strWhere))
                strWhere = " where " + strWhere + " ";

            StringBuilder sql = new StringBuilder();
            sql.Append(" select " + topnums);
            sql.Append(getFileds);
            sql.Append(" from " + tblName);
            sql.Append(strWhere);
            sql.Append(orders);

            var list = db.Ado.SqlQuery<T>(sql.ToString(), listSugarParameter);
            return list;
        }

        /// <summary>
        /// 分页查询：读取实体数据,根据orders分页
        /// </summary>
        public List<T> GetPageList<T>(string tblName, string getFileds, string orders, int pageSize, int currentPageIndex, out int allcount, string strWhere, params object[] parameterValue) where T : new()
        {
            strWhere = SqlStatementParametered(strWhere);
            List<SugarParameter> listSugarParameter = ChangeSugarParameter(parameterValue);

            // 分页方法一：ROW_NUMBER()
            //         string sql=@"   SELECT  * 
            // FROM 
            //         (
            //        SELECT ROW_NUMBER() OVER (ORDER BY MenuID) AS RowNumber,* FROM Users.Menu
            //         ) A
            // WHERE (RowNumber>10*(2-1) and RowNumber <= 10*2)";

            //分页查询
            StringBuilder sql = new StringBuilder();
            sql.Append(" SELECT  * FROM (");
            sql.Append(" SELECT " + getFileds + ",ROW_NUMBER() OVER (ORDER BY " + orders + ") AS RowNumber FROM " + tblName);
            if (!string.IsNullOrEmpty(strWhere)) sql.Append(" where " + strWhere);
            sql.Append(" ) A");
            sql.Append(" WHERE (RowNumber>" + pageSize + "*" + (currentPageIndex - 1) + " and RowNumber <= " + pageSize + "*" + currentPageIndex + ")");
            var list = db.Ado.SqlQuery<T>(sql.ToString(), listSugarParameter);

            //查询总数
            sql.Clear();
            sql.Append(" select count(0) from " + tblName);
            if (!string.IsNullOrEmpty(strWhere)) sql.Append(" where " + strWhere);
            var resultValue = db.Ado.GetScalar(sql.ToString(), listSugarParameter);
            Int32.TryParse(resultValue.ToString(), out allcount);

            return list;

        }


        private List<SugarParameter> ChangeSugarParameter(object[] parameterValue)
        {
            List<SugarParameter> list = new List<SugarParameter>();
            if (parameterValue != null)
            {
                for (int i = 0; i < parameterValue.Length; i++)
                {
                    list.Add(new SugarParameter("@" + i, parameterValue[i]));
                }
            }
            return list;

        }
        /// <summary>
        /// sql语句参数化
        /// </summary>
        private string SqlStatementParametered(string sqlStatement)
        {
            if (!string.IsNullOrEmpty(sqlStatement))
            {
                return sqlStatement.Replace("{", "@").Replace("}", "");
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 删除
        /// <summary>
        /// 删除   以主键做为条件
        /// </summary>

        #endregion

        #region 修改
        /// <summary>
        /// 修改一条记录 以主键做为条件
        /// </summary>



        #endregion

        #region 添加
        /// <summary>
        /// 添加一条记录 返回主键值
        /// </summary>

        #endregion
    }
}
